package com.edu.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.edu.bean.Cars;
import com.edu.dao.BaseDao;
import com.edu.dao.CarDao;

public class CarDaoImpl implements CarDao {

	@Override
	public List<Cars> getAll(int pageNum, int pageSize) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<Cars> cars = new ArrayList();
		String sql = "SELECT * FROM cars ORDER BY carId DESC LIMIT ?,?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, (pageNum-1)*pageSize);
			ps.setInt(2, pageSize);
			res = ps.executeQuery();
			while(res.next()) {
				Cars car = new Cars();
				car.setCarid(res.getInt("carid"));
			    car.setCarname(res.getString("carname"));
			    car.setCarimg(res.getString("carimg"));
			    car.setPrice(res.getDouble("price"));
			    car.setSaleState(res.getInt("saleState"));
			    car.setIntroduce(res.getString("introduce"));
			    car.setType(res.getString("type"));
			    car.setBrand(res.getString("brand"));
			    cars.add(car);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return cars;
	}

	@Override
	public Cars getById(int id) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		Cars car=null;
		String sql = "SELECT * FROM cars WHERE carId = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,id);
			res = ps.executeQuery();
			if(res.next()) {
				car = new Cars();
				car.setCarid(res.getInt("carid"));
			    car.setCarname(res.getString("carname"));
			    car.setCarimg(res.getString("carimg"));
			    car.setPrice(res.getDouble("price"));
			    car.setSaleState(res.getInt("saleState"));
			    car.setIntroduce(res.getString("introduce"));
			    car.setType(res.getString("type"));
			    car.setBrand(res.getString("brand"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return car;
	}

	@Override
	public int selectUserAll(Cars car) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT count(1) as total FROM cars WHERE 1=1 ";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
		
	}

	@Override
	public int update(Cars car) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE cars set "
				+ "carName = ?,price = ?,saleState= ?,introduce= ?,type= ?,brand= ?";
				if(car.getCarimg()!=null) {
					sql +=",carImg = ?";
				}
				sql+= " WHERE carId = ? ";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, car.getCarname());
			ps.setDouble(2, car.getPrice());
			ps.setInt(3, car.getSaleState());
			ps.setString(4,car.getIntroduce());
			ps.setString(5,car.getType());
			ps.setString(6,car.getBrand());
			if(car.getCarimg()!=null) {
				ps.setString(7,car.getCarimg());
				ps.setInt(8, car.getCarid());
			}else {
				ps.setInt(7, car.getCarid());
			}
			result = ps.executeUpdate();
			System.out.println(ps);
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public int delete(int id) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "DELETE FROM cars WHERE carId = ?";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		
		return result;
	}

	@Override
	public int addcar(Cars cars) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql="";
		if(cars.getCarimg() == null) {
			sql = "INSERT INTO cars(carName,price,saleState,introduce,type,brand) VALUES (?,?,?,?,?,?)";
		}else {
			sql = "INSERT INTO cars(carName,price,saleState,introduce,type,brand,carImg) VALUES (?,?,?,?,?,?,?)";
		}
		
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, cars.getCarname());
			ps.setDouble(2, cars.getPrice());
			ps.setInt(3, cars.getSaleState());
			ps.setString(4,cars.getIntroduce());
			ps.setString(5,cars.getType());
			ps.setString(6,cars.getBrand());
			if(cars.getCarimg() != null) {
				ps.setString(7, cars.getCarimg());
			}
			result = ps.executeUpdate();
			System.out.println(ps);
		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public List<Cars> selectCarsAll() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<Cars> cars = new ArrayList();
		String sql = "SELECT * FROM cars";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			while(res.next()) {
				Cars car = new Cars();
				car.setCarid(res.getInt("carid"));
			    car.setCarname(res.getString("carname"));
			    car.setCarimg(res.getString("carimg"));
			    car.setPrice(res.getDouble("price"));
			    car.setSaleState(res.getInt("saleState"));
			    car.setIntroduce(res.getString("introduce"));
			    car.setType(res.getString("type"));
			    car.setBrand(res.getString("brand"));
			    cars.add(car);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return cars;
	}

	@Override
	public int selectCarjc() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT count(1) as total FROM cars WHERE type='轿车'";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int selectCarpc() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT count(1) as total FROM cars WHERE type='跑车'";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int selectCarSuv() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT count(1) as total FROM cars WHERE type='SUV'";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}
}
